package org.wll.reporsity;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.wll.dto.DeptEmpDto;
import org.wll.entity.Department;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface DepartmentRepository extends JpaRepository<Department, Integer>, JpaSpecificationExecutor<Department> {


    /**
     * 纯 sql 语句实现 多表查询
     *
     * @param did
     * @return
     */
    @Query(value = "SELECT td.did, td.dname, td.deptno, td.loc, te.eid, te.empno, te.ename, te.sal " +
            "from tb_department td  LEFT JOIN tb_employee te ON te.e_did = td.did  where td.did = :did", nativeQuery = true)
    List<Map> findDeptAllEmpByDid(@Param("did") Integer did);

    /**
     * 纯 sql 语句实现 多表查询  分页
     *
     * @param did
     * @return
     */
    @Query(value = "SELECT td.did, td.dname, td.deptno, td.loc, te.eid, te.empno, te.ename, te.sal " +
            "from tb_department td  LEFT JOIN tb_employee te ON te.e_did = td.did  where td.did = :did ",
            countQuery = "select count(*) from tb_department td  LEFT JOIN tb_employee te ON te.e_did = td.did where td.did = :did ",
            nativeQuery = true)
    Page<Map> findDeptAllEmpPageByDid(@Param("did") Integer did, Pageable pageable);

    /**
     * 自定义分页 总数
     *
     * @param did
     * @return
     */
    @Query(value = "select count(*) from tb_department td  LEFT JOIN tb_employee te ON te.e_did = td.did where td.did = :did ",
            nativeQuery = true)
    Integer findDeptAllEmpPageByDidCount(@Param("did") Integer did);

    /**
     * 自定义分页查询
     *
     * @param did
     * @param start 开始
     * @param end   结束
     * @return
     */
    @Query(value = "SELECT td.did, td.dname, td.deptno, td.loc, te.eid, te.empno, te.ename, te.sal " +
            "from tb_department td  LEFT JOIN tb_employee te ON te.e_did = td.did  where td.did = :did limit :start, :end ",
            nativeQuery = true)
    List<Map> findDeptAllEmpPage2ByDid(@Param("did") Integer did, @Param("start") Integer start, @Param("end") Integer end);
}
